package com.cl.blog.mapper;

import com.cl.blog.entity.BContent;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cl.blog.po.InterestPo;
import com.cl.blog.po.MonthPo;
import com.cl.blog.po.PigeonholePo;
import com.cl.blog.vo.ContentSearchVo;
import com.cl.blog.vo.YearPo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author object
 * @description 针对表【b_content】的数据库操作Mapper
 * @createDate 2024-03-14 17:13:59
 * @Entity com.cl.blog.entity.BContent
 */
@Mapper
public interface BContentMapper extends BaseMapper<BContent> {

	@Select("select * from b_content where `status` in (#{i},#{i1}) ORDER BY create_time")
	List<BContent> selectByTwoStatus(@Param("i") int i, @Param("i1") int i1);

	@Select("select * from b_content where status=#{i} ")
	List<BContent> selectByStatus(int i);

	Long selectLikeCount(@Param("id") Integer id);

	Long getCollectCount(@Param("user_id") Integer id);

	Long getCollectCountWeek(@Param("user_id") Integer id);

	Long getLikeCount(@Param("userId") Integer id);

	Long getHistory(@Param("userId") Integer id);

	void addLike(@Param("contentId") Integer contentId);

	void subtractLike(@Param("contentId") Integer contentId);

	void addCollect(@Param("contentId") Integer contentId);

	void subtractCollect(@Param("contentId") Integer contentId);

	List<Object> getYearCount(@Param("userId") Integer userId);

	List<Object> getTypeCount(@Param("userId") Integer userId);

	void cancelLike(@Param("id") Integer id);

	void cancelCollect(@Param("id") Integer id);

	List<PigeonholePo> selectByPigeonhole();

	/**
	 * 根据年份统计
	 *
	 * @return
	 */
	@Select("WITH RECURSIVE YearRange AS (  \n" +
			"    SELECT YEAR(CURRENT_DATE) - 0 AS year_val  \n" +
			"    UNION ALL  \n" +
			"    SELECT year_val - 1 FROM YearRange WHERE year_val > YEAR(CURRENT_DATE) - 9 \n" +
			")  \n" +
			"SELECT   \n" +
			"    yr.year_val AS year,  \n" +
			"    COALESCE(COUNT(yt.create_time), 0) AS count  \n" +
			"FROM   \n" +
			"    YearRange yr  \n" +
			"LEFT JOIN   \n" +
			"    b_content yt ON yr.year_val = YEAR(yt.create_time)  \n" +
			"GROUP BY   \n" +
			"    yr.year_val  \n" +
			"ORDER BY   \n" +
			"    yr.year_val ASC;")
	List<YearPo> getYearByCount();

	/**
	 * 根据年份统计月份数据
	 *
	 * @return
	 */
	@Select("WITH RECURSIVE month_sequence AS (  \n" +
			"    SELECT 1 AS month_num  \n" +
			"    UNION ALL  \n" +
			"    SELECT month_num + 1 FROM month_sequence WHERE month_num < 12  \n" +
			"),  \n" +
			"months_in_year AS (  \n" +
			"    SELECT  \n" +
			"        month_num,  \n" +
			"        DATE_FORMAT(DATE_ADD(CONCAT(#{year},'-01-01'), INTERVAL (month_num - 1) MONTH), '%Y-%m') AS month_str  \n" +
			"    FROM month_sequence  \n" +
			")  \n" +
			"SELECT  \n" +
			"    my.month_str AS month,  \n" +
			"    COALESCE(COUNT(bc.id), 0) AS count  \n" +
			"FROM months_in_year my  \n" +
			"LEFT JOIN b_content bc ON YEAR(bc.create_time) = #{year}  \n" +
			"                      AND MONTH(bc.create_time) = my.month_num  \n" +
			"GROUP BY my.month_str  \n" +
			"ORDER BY my.month_str;")
	List<MonthPo> getMonthByYear(@Param("year") String year);


}




